﻿using NLog;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Threading.Tasks;
using System.Web.Http;
using WTAppletProjectHTTPS.Models;

namespace WTAppletProjectHTTPS.Controllers
{
   
    public class ColaFinalInspectionRecordQueryController : ApiController
    {
        private static readonly Logger Logger = NLog.LogManager.GetCurrentClassLogger();

        // POST api/<controller>
        public async Task<HttpResponseMessage> Post([FromBody] ColaFinalInspectionRecordQuery CFIRQ)
        {
            ReturnObjectQuery RO = new ReturnObjectQuery();
            if (CFIRQ.PageNumber <= 0 || CFIRQ.PageSize <= 0)
            {
                Logger.Warn("分页参数无效");
                RO.Status = HttpStatusCode.BadRequest;
                RO.Message = "分页参数无效";
                RO.Data = null;
                Logger.Error("分页参数无效");
                return Request.CreateResponse(HttpStatusCode.BadRequest, RO);
            }

            try
            {
                using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionName"].ConnectionString))
                {
                    await connection.OpenAsync();

                    var whereClause = "WHERE 1=1";
                    if (CFIRQ.CFIR_ID != 0)
                    {
                        whereClause += " AND CFIRID = @ID";
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_Inspector))
                    {
                        whereClause += " AND CFIRInspector LIKE @Inspector";
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_Shift))
                    {
                        whereClause += " AND CFIRShift LIKE @Shift";
                    }
                    if (CFIRQ.CFIR_InspectionTime.HasValue)
                    {
                        whereClause += " AND CFIRInspectionTime = @InspectionTime";
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_LineParting))
                    {
                        whereClause += " AND CFIRLineParting LIKE @LineParting";
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_OrderNumber))
                    {
                        whereClause += " AND CFIROrderNumber LIKE @OrderNumber";
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_ProductModel))
                    {
                        whereClause += " AND CFIRProductModel LIKE @ProductModel";
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_InspectionItem))
                    {
                        whereClause += " AND CFIRInspectionItem LIKE @InspectionItem";
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_C1DisconnectionDefect))
                    {
                        whereClause += " AND CFIRC1DisconnectionDefect LIKE @C1DisconnectionDefect";
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_C2ProtectionGateDefect))
                    {
                        whereClause += " AND CFIRC2ProtectionGateDefect LIKE @C2ProtectionGateDefect";
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_C3HandFeelDefect))
                    {
                        whereClause += " AND CFIRC3HandFeelDefect LIKE @C3HandFeelDefect";
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_C4AppearanceDefect))
                    {
                        whereClause += " AND CFIRC4AppearanceDefect LIKE @C4AppearanceDefect";
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_C5PadPrintingLaserDefect))
                    {
                        whereClause += " AND CFIRC5PadPrintingLaserDefect LIKE @C5PadPrintingLaserDefect";
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_C6AssemblyDefect))
                    {
                        whereClause += " AND CFIRC6AssemblyDefect LIKE @C6AssemblyDefect";
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_C7EquipmentJigDefect))
                    {
                        whereClause += " AND CFIRC7EquipmentJigDefect LIKE @C7EquipmentJigDefect";
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_C8MarkingDefect))
                    {
                        whereClause += " AND CFIRC8MarkingDefect LIKE @C8MarkingDefect";
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_C9InjectionMoldedPartDefect))
                    {
                        whereClause += " AND CFIRC9InjectionMoldedPartDefect LIKE @C9InjectionMoldedPartDefect";
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_C10PackagingDefect))
                    {
                        whereClause += " AND CFIRC10PackagingDefect LIKE @C10PackagingDefect";
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_C11VoltageResistanceDefect))
                    {
                        whereClause += " AND CFIRC11VoltageResistanceDefect LIKE @C11VoltageResistanceDefect";
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_C12OtherDefects))
                    {
                        whereClause += " AND CFIRC12OtherDefects LIKE @C12OtherDefects";
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_InspectionConclusion))
                    {
                        whereClause += " AND CFIRInspectionConclusion LIKE @InspectionConclusion";
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_WhichInspectionQualified))
                    {
                        whereClause += " AND CFIRWhichInspectionQualified LIKE @WhichInspectionQualified";
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_NonconformanceDescription1))
                    {
                        whereClause += " AND CFIRNonconformanceDescription1 LIKE @NonconformanceDescription1";
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_NonconformanceImage1))
                    {
                        whereClause += " AND CFIRNonconformanceImage1 LIKE @NonconformanceImage1";
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_NonconformanceDescription2))
                    {
                        whereClause += " AND CFIRNonconformanceDescription2 LIKE @NonconformanceDescription2";
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_NonconformanceImage2))
                    {
                        whereClause += " AND CFIRNonconformanceImage2 LIKE @NonconformanceImage2";
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_NonconformanceDescription3))
                    {
                        whereClause += " AND CFIRNonconformanceDescription3 LIKE @NonconformanceDescription3";
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_NonconformanceImage3))
                    {
                        whereClause += " AND CFIRNonconformanceImage3 LIKE @NonconformanceImage3";
                    }

                    var query = $@"
                        SELECT 
                            CFIRID, CFIRInspector, CFIRShift, CFIRInspectionTime, CFIRLineParting, 
                            CFIROrderNumber, CFIRProductModel, CFIRBatchQuantity, CFIRSampleQuantity, 
                            CFIRInspectionItem, CFIRC1DisconnectionDefect, CFIRC2ProtectionGateDefect, 
                            CFIRC3HandFeelDefect, CFIRC4AppearanceDefect, CFIRC5PadPrintingLaserDefect, 
                            CFIRC6AssemblyDefect, CFIRC7EquipmentJigDefect, CFIRC8MarkingDefect, 
                            CFIRC9InjectionMoldedPartDefect, CFIRC10PackagingDefect, CFIRC11VoltageResistanceDefect, 
                            CFIRC12OtherDefects, CFIRInspectionConclusion, CFIRWhichInspectionQualified, 
                            CFIRNonconformanceDescription1, CFIRNonconformanceImage1, CFIRNonconformanceDescription2, 
                            CFIRNonconformanceImage2, CFIRNonconformanceDescription3, CFIRNonconformanceImage3,CFIRCreateCreator,CFIRCreateTime,
                            COUNT(*) OVER() AS TotalCount
                        FROM 
                            (SELECT 
                                CFIRID, CFIRInspector, CFIRShift, CFIRInspectionTime, CFIRLineParting, 
                                CFIROrderNumber, CFIRProductModel, CFIRBatchQuantity, CFIRSampleQuantity, 
                                CFIRInspectionItem, CFIRC1DisconnectionDefect, CFIRC2ProtectionGateDefect, 
                                CFIRC3HandFeelDefect, CFIRC4AppearanceDefect, CFIRC5PadPrintingLaserDefect, 
                                CFIRC6AssemblyDefect, CFIRC7EquipmentJigDefect, CFIRC8MarkingDefect, 
                                CFIRC9InjectionMoldedPartDefect, CFIRC10PackagingDefect, CFIRC11VoltageResistanceDefect, 
                                CFIRC12OtherDefects, CFIRInspectionConclusion, CFIRWhichInspectionQualified, 
                                CFIRNonconformanceDescription1, CFIRNonconformanceImage1, CFIRNonconformanceDescription2, 
                                CFIRNonconformanceImage2, CFIRNonconformanceDescription3, CFIRNonconformanceImage3,CFIRCreateCreator,CFIRCreateTime, 
                                ROW_NUMBER() OVER(ORDER BY CFIRID) AS RowNum
                             FROM ColaFinalInspectionRecord {whereClause}) AS Result
                        WHERE 
                            RowNum BETWEEN @StartRow AND @EndRow
                        ORDER BY RowNum";

                    int startRow = (CFIRQ.PageNumber - 1) * CFIRQ.PageSize + 1;
                    int endRow = CFIRQ.PageNumber * CFIRQ.PageSize;

                    var parameters = new List<SqlParameter>
                    {
                        new SqlParameter("@StartRow", SqlDbType.Int) { Value = startRow },
                        new SqlParameter("@EndRow", SqlDbType.Int) { Value = endRow }
                    };
                    if (CFIRQ.CFIR_ID != 0)
                    {
                        parameters.Add(new SqlParameter("@ID", SqlDbType.Int) { Value = CFIRQ.CFIR_ID });
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_Inspector))
                    {
                        parameters.Add(new SqlParameter("@Inspector", SqlDbType.NVarChar, 50) { Value = $"%{CFIRQ.CFIR_Inspector}%" });
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_Shift))
                    {
                        parameters.Add(new SqlParameter("@Shift", SqlDbType.NVarChar, 50) { Value = $"%{CFIRQ.CFIR_Shift}%" });
                    }
                    if (CFIRQ.CFIR_InspectionTime.HasValue)
                    {
                        parameters.Add(new SqlParameter("@InspectionTime", SqlDbType.DateTime) { Value = CFIRQ.CFIR_InspectionTime.Value });
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_LineParting))
                    {
                        parameters.Add(new SqlParameter("@LineParting", SqlDbType.NVarChar, 50) { Value = $"%{CFIRQ.CFIR_LineParting}%" });
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_OrderNumber))
                    {
                        parameters.Add(new SqlParameter("@OrderNumber", SqlDbType.NVarChar, 50) { Value = $"%{CFIRQ.CFIR_OrderNumber}%" });
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_ProductModel))
                    {
                        parameters.Add(new SqlParameter("@ProductModel", SqlDbType.NVarChar, 50) { Value = $"%{CFIRQ.CFIR_ProductModel}%" });
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_InspectionItem))
                    {
                        parameters.Add(new SqlParameter("@InspectionItem", SqlDbType.NVarChar, 50) { Value = $"%{CFIRQ.CFIR_InspectionItem}%" });
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_C1DisconnectionDefect))
                    {
                        parameters.Add(new SqlParameter("@C1DisconnectionDefect", SqlDbType.NVarChar, 50) { Value = $"%{CFIRQ.CFIR_C1DisconnectionDefect}%" });
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_C2ProtectionGateDefect))
                    {
                        parameters.Add(new SqlParameter("@C2ProtectionGateDefect", SqlDbType.NVarChar, 50) { Value = $"%{CFIRQ.CFIR_C2ProtectionGateDefect}%" });
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_C3HandFeelDefect))
                    {
                        parameters.Add(new SqlParameter("@C3HandFeelDefect", SqlDbType.NVarChar, 50) { Value = $"%{CFIRQ.CFIR_C3HandFeelDefect}%" });
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_C4AppearanceDefect))
                    {
                        parameters.Add(new SqlParameter("@C4AppearanceDefect", SqlDbType.NVarChar, 50) { Value = $"%{CFIRQ.CFIR_C4AppearanceDefect}%" });
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_C5PadPrintingLaserDefect))
                    {
                        parameters.Add(new SqlParameter("@C5PadPrintingLaserDefect", SqlDbType.NVarChar, 50) { Value = $"%{CFIRQ.CFIR_C5PadPrintingLaserDefect}%" });
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_C6AssemblyDefect))
                    {
                        parameters.Add(new SqlParameter("@C6AssemblyDefect", SqlDbType.NVarChar, 50) { Value = $"%{CFIRQ.CFIR_C6AssemblyDefect}%" });
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_C7EquipmentJigDefect))
                    {
                        parameters.Add(new SqlParameter("@C7EquipmentJigDefect", SqlDbType.NVarChar, 50) { Value = $"%{CFIRQ.CFIR_C7EquipmentJigDefect}%" });
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_C8MarkingDefect))
                    {
                        parameters.Add(new SqlParameter("@C8MarkingDefect", SqlDbType.NVarChar, 50) { Value = $"%{CFIRQ.CFIR_C8MarkingDefect}%" });
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_C9InjectionMoldedPartDefect))
                    {
                        parameters.Add(new SqlParameter("@C9InjectionMoldedPartDefect", SqlDbType.NVarChar, 50) { Value = $"%{CFIRQ.CFIR_C9InjectionMoldedPartDefect}%" });
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_C10PackagingDefect))
                    {
                        parameters.Add(new SqlParameter("@C10PackagingDefect", SqlDbType.NVarChar, 50) { Value = $"%{CFIRQ.CFIR_C10PackagingDefect}%" });
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_C11VoltageResistanceDefect))
                    {
                        parameters.Add(new SqlParameter("@C11VoltageResistanceDefect", SqlDbType.NVarChar, 50) { Value = $"%{CFIRQ.CFIR_C11VoltageResistanceDefect}%" });
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_C12OtherDefects))
                    {
                        parameters.Add(new SqlParameter("@C12OtherDefects", SqlDbType.NVarChar, 50) { Value = $"%{CFIRQ.CFIR_C12OtherDefects}%" });
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_InspectionConclusion))
                    {
                        parameters.Add(new SqlParameter("@InspectionConclusion", SqlDbType.NVarChar, 50) { Value = $"%{CFIRQ.CFIR_InspectionConclusion}%" });
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_WhichInspectionQualified))
                    {
                        parameters.Add(new SqlParameter("@WhichInspectionQualified", SqlDbType.NVarChar, 50) { Value = $"%{CFIRQ.CFIR_WhichInspectionQualified}%" });
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_NonconformanceDescription1))
                    {
                        parameters.Add(new SqlParameter("@NonconformanceDescription1", SqlDbType.NVarChar, 100) { Value = $"%{CFIRQ.CFIR_NonconformanceDescription1}%" });
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_NonconformanceImage1))
                    {
                        parameters.Add(new SqlParameter("@NonconformanceImage1", SqlDbType.NVarChar, 100) { Value = $"%{CFIRQ.CFIR_NonconformanceImage1}%" });
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_NonconformanceDescription2))
                    {
                        parameters.Add(new SqlParameter("@NonconformanceDescription2", SqlDbType.NVarChar, 100) { Value = $"%{CFIRQ.CFIR_NonconformanceDescription2}%" });
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_NonconformanceImage2))
                    {
                        parameters.Add(new SqlParameter("@NonconformanceImage2", SqlDbType.NVarChar, 100) { Value = $"%{CFIRQ.CFIR_NonconformanceImage2}%" });
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_NonconformanceDescription3))
                    {
                        parameters.Add(new SqlParameter("@NonconformanceDescription3", SqlDbType.NVarChar, 100) { Value = $"%{CFIRQ.CFIR_NonconformanceDescription3}%" });
                    }
                    if (!string.IsNullOrEmpty(CFIRQ.CFIR_NonconformanceImage3))
                    {
                        parameters.Add(new SqlParameter("@NonconformanceImage3", SqlDbType.NVarChar, 100) { Value = $"%{CFIRQ.CFIR_NonconformanceImage3}%" });
                    }

                    using (var command = new SqlCommand(query, connection))
                    {
                        command.Parameters.AddRange(parameters.ToArray());
                        using (var reader = await command.ExecuteReaderAsync())
                        {
                            if (!reader.HasRows)
                            {
                                Logger.Warn("未找到记录");
                                RO.Status = HttpStatusCode.NotFound;
                                RO.Message = "未找到记录";
                                RO.Data = null;
                                return Request.CreateResponse(HttpStatusCode.NotFound, RO);
                            }

                            var records = new List<ColaFinalInspectionRecord>();
                            int totalCount = 0;
                            while (await reader.ReadAsync())
                            {
                                if (totalCount == 0)
                                {
                                    totalCount = reader.GetInt32(reader.GetOrdinal("TotalCount"));
                                }

                                records.Add(new ColaFinalInspectionRecord
                                {
                                    CFIR_ID = reader.GetInt32(reader.GetOrdinal("CFIRID")),
                                    CFIR_Inspector = reader.GetString(reader.GetOrdinal("CFIRInspector")),
                                    CFIR_Shift = reader.GetString(reader.GetOrdinal("CFIRShift")),
                                    CFIR_InspectionTime = reader.GetDateTime(reader.GetOrdinal("CFIRInspectionTime")),
                                    CFIR_LineParting = reader.GetString(reader.GetOrdinal("CFIRLineParting")),
                                    CFIR_OrderNumber = reader.GetString(reader.GetOrdinal("CFIROrderNumber")),
                                    CFIR_ProductModel = reader.GetString(reader.GetOrdinal("CFIRProductModel")),
                                    CFIR_BatchQuantity = reader.GetInt32(reader.GetOrdinal("CFIRBatchQuantity")),
                                    CFIR_SampleQuantity = reader.GetInt32(reader.GetOrdinal("CFIRSampleQuantity")),
                                    CFIR_InspectionItem = reader.GetString(reader.GetOrdinal("CFIRInspectionItem")),
                                    CFIR_C1DisconnectionDefect = reader.GetString(reader.GetOrdinal("CFIRC1DisconnectionDefect")),
                                    CFIR_C2ProtectionGateDefect = reader.GetString(reader.GetOrdinal("CFIRC2ProtectionGateDefect")),
                                    CFIR_C3HandFeelDefect = reader.GetString(reader.GetOrdinal("CFIRC3HandFeelDefect")),
                                    CFIR_C4AppearanceDefect = reader.GetString(reader.GetOrdinal("CFIRC4AppearanceDefect")),
                                    CFIR_C5PadPrintingLaserDefect = reader.GetString(reader.GetOrdinal("CFIRC5PadPrintingLaserDefect")),
                                    CFIR_C6AssemblyDefect = reader.GetString(reader.GetOrdinal("CFIRC6AssemblyDefect")),
                                    CFIR_C7EquipmentJigDefect = reader.GetString(reader.GetOrdinal("CFIRC7EquipmentJigDefect")),
                                    CFIR_C8MarkingDefect = reader.GetString(reader.GetOrdinal("CFIRC8MarkingDefect")),
                                    CFIR_C9InjectionMoldedPartDefect = reader.GetString(reader.GetOrdinal("CFIRC9InjectionMoldedPartDefect")),
                                    CFIR_C10PackagingDefect = reader.GetString(reader.GetOrdinal("CFIRC10PackagingDefect")),
                                    CFIR_C11VoltageResistanceDefect = reader.GetString(reader.GetOrdinal("CFIRC11VoltageResistanceDefect")),
                                    CFIR_C12OtherDefects = reader.GetString(reader.GetOrdinal("CFIRC12OtherDefects")),
                                    CFIR_InspectionConclusion = reader.GetString(reader.GetOrdinal("CFIRInspectionConclusion")),
                                    CFIR_WhichInspectionQualified = reader.GetString(reader.GetOrdinal("CFIRWhichInspectionQualified")),
                                    CFIR_NonconformanceDescription1 = reader.GetString(reader.GetOrdinal("CFIRNonconformanceDescription1")),
                                    CFIR_NonconformanceImage1 = reader.GetString(reader.GetOrdinal("CFIRNonconformanceImage1")),
                                    CFIR_NonconformanceDescription2 = reader.GetString(reader.GetOrdinal("CFIRNonconformanceDescription2")),
                                    CFIR_NonconformanceImage2 = reader.GetString(reader.GetOrdinal("CFIRNonconformanceImage2")),
                                    CFIR_NonconformanceDescription3 = reader.GetString(reader.GetOrdinal("CFIRNonconformanceDescription3")),
                                    CFIR_NonconformanceImage3 = reader.GetString(reader.GetOrdinal("CFIRNonconformanceImage3")),
                                    CFIR_CreateCreator = reader.GetString(reader.GetOrdinal("CFIRCreateCreator")),
                                    CFIR_CreateTime = reader.GetDateTime(reader.GetOrdinal("CFIRCreateTime"))
                                });
                            }

                            Logger.Info("查询成功");
                            RO.Status = HttpStatusCode.OK;
                            RO.Message = "OK";
                            RO.Data = records;
                            RO.TotalCount = totalCount;
                            RO.PageNumber = CFIRQ.PageNumber;
                            RO.PageSize = CFIRQ.PageSize;
                            return Request.CreateResponse(HttpStatusCode.OK, RO);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Logger.Error("查询失败: " + ex.Message);
                RO.Status = HttpStatusCode.InternalServerError;
                RO.Message = "Error: " + ex.Message;
                RO.Data = null;
                return Request.CreateResponse(HttpStatusCode.InternalServerError, RO);
            }
        }
    }
}
